package edu.ubb.scrumster.model.repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import edu.ubb.scrumster.model.model.Comment;
import edu.ubb.scrumster.model.model.AbstractModel;
import edu.ubb.scrumster.model.model.Estimation;
import edu.ubb.scrumster.model.model.Notification;
import edu.ubb.scrumster.model.model.Organization;
import edu.ubb.scrumster.model.model.Project;
import edu.ubb.scrumster.model.model.Scrum;
import edu.ubb.scrumster.model.model.Sprint;
import edu.ubb.scrumster.model.model.Task;
import edu.ubb.scrumster.model.model.User;
import edu.ubb.scrumster.model.model.UserStory;

public class DatabaseAccess {
	public static final int COMMENT = 0;
	public static final int ESTIMATION = 1;
	public static final int NOTIFICATION = 2;
	public static final int ORGANIZATION = 3;
	public static final int PROJECT = 4;
	public static final int SCRUM = 5;
	public static final int SPRINT = 6;
	public static final int TASK = 7;
	public static final int USERSTORY = 8;
	public static final int USER = 9;
	private Connection connection = null;
	private Statement statement = null;
	private java.sql.PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	public static DatabaseAccess instance;

	public static DatabaseAccess getInstance() {
		if (instance == null) {
			instance = new DatabaseAccess();
		}
		return instance;
	}

	public DatabaseAccess() {
		connectionToDB();
	}

	private void connectionToDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/scrumster", "root", "anna2625");
			statement = (Statement) connection.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void addData(AbstractModel data) {
		try {
			if (data instanceof Comment) {
				preparedStatement = connection
						.prepareStatement("insert into comment (userId,date,text) values (?,?,?)");
				preparedStatement.setInt(1, ((Comment) data).getUser().getId());
				preparedStatement.setDate(2,((Comment) data).getDate());
				preparedStatement.setString(3, ((Comment) data).getText());
			}

			else if (data instanceof Project) {
				if (((Project) data).getOwner() != null) {
					preparedStatement = connection
							.prepareStatement("insert into project (name, ownerId, description,specification, status) values (?,?,?,?,?)");
					preparedStatement.setString(1, ((Project) data).getName());
					preparedStatement.setInt(2, ((Project) data).getOwner()
							.getId());
					preparedStatement.setString(3,
							((Project) data).getDescription());
					preparedStatement.setBytes(4,
							((Project) data).getSpecification());
					preparedStatement
							.setString(5, ((Project) data).getStatus());
				} else {
					preparedStatement = connection
							.prepareStatement("insert into project (name, description,specification, status) values (?,?,?,?)");
					preparedStatement.setString(1, ((Project) data).getName());
					preparedStatement.setString(2,
							((Project) data).getDescription());
					preparedStatement.setBytes(3,
							((Project) data).getSpecification());
					preparedStatement
							.setString(4, ((Project) data).getStatus());
					}

			} else if (data instanceof UserStory) {
				if (((UserStory) data).getSprint() != null) {
					preparedStatement = connection
							.prepareStatement("insert into userStory (description, sprintId) values (?,?)");

					preparedStatement.setString(1,
							((UserStory) data).getDescription());
					preparedStatement.setInt(2, ((UserStory) data).getSprint()
							.getId());
				} else {
					preparedStatement = connection
							.prepareStatement("insert into userStory (description) values (?)");
					preparedStatement.setString(1,
							((UserStory) data).getDescription());
				}

			} else if (data instanceof Organization) {
				preparedStatement = connection
						.prepareStatement("insert into organization (name) values (?)");
				preparedStatement.setString(1, ((Organization) data).getName());
			}

			else if (data instanceof Notification) {
				preparedStatement = connection
						.prepareStatement("insert into notification (senderId,receiverId,message,seen) values (?,?,?,?)");
				preparedStatement.setInt(1, ((Notification) data).getSender()
						.getId());
				preparedStatement.setInt(2, ((Notification) data).getReceiver()
						.getId());
				preparedStatement.setString(3,
						((Notification) data).getMessage());
				preparedStatement
						.setBoolean(4, ((Notification) data).getSeen());
			}

			else if (data instanceof Estimation) {
				preparedStatement = connection
						.prepareStatement("insert into estimation (userId,taskId,time,priority) values (?,?,?,?)");
				preparedStatement.setInt(1, ((Estimation) data).getUser()
						.getId());
				preparedStatement.setInt(2, ((Estimation) data).getTask()
						.getId());
				preparedStatement.setInt(3, ((Estimation) data).getTime());
				preparedStatement.setInt(4, ((Estimation) data).getPriority());
			}

			else if (data instanceof Scrum) {
				if (((Scrum) data).getProject() != null) {
					preparedStatement = connection
							.prepareStatement("insert into scrum (name, projectId) values (?,?)");
					preparedStatement.setString(1, ((Scrum) data).getName());
					preparedStatement.setInt(2, ((Scrum) data).getProject()
							.getId());
				} else {
					preparedStatement = connection
							.prepareStatement("insert into scrum (name) values (?)");
					preparedStatement.setString(1, ((Scrum) data).getName());
				}
			}

			else if (data instanceof Task) {
				if (((Task) data).getUser() != null) {
					preparedStatement = connection
							.prepareStatement("insert into task (userstory, description, userId, status) values (?,?,?,?)");
					preparedStatement.setInt(1, ((Task) data).getUserStory()
							.getId());
					preparedStatement.setString(2,
							((Task) data).getDescription());
					preparedStatement
							.setInt(3, ((Task) data).getUser().getId());
					preparedStatement.setString(4, ((Task) data).getStatus());

				} else {
					preparedStatement = connection
							.prepareStatement("insert into task (userstory, description, status) values (?,?,?)");
					preparedStatement.setInt(1, ((Task) data).getUserStory()
							.getId());
					preparedStatement.setString(2,
							((Task) data).getDescription());
					preparedStatement.setString(3, ((Task) data).getStatus());
				}

			} else if (data instanceof User) {
				if (((User) data).getScrum() != null) {
					preparedStatement = connection
							.prepareStatement("insert into user (username, password, firstName, lastName, email, scrumId, organizationId, type) values (?,?,?,?,?,?,?,?)");
					preparedStatement.setString(1, ((User) data).getUsername());
					preparedStatement.setString(2, ((User) data).getPassword());
					preparedStatement
							.setString(3, ((User) data).getFirstName());
					preparedStatement.setString(4, ((User) data).getLastName());
					preparedStatement.setString(5, ((User) data).getEmail());
					preparedStatement.setInt(6, ((User) data).getScrum()
							.getId());
					preparedStatement.setInt(7, ((User) data).getOrganization()
							.getId());
					preparedStatement.setString(8, ((User) data).getType());

				} else {
					preparedStatement = connection
							.prepareStatement("insert into user (username, password, firstName, lastName, email, organizationId, type) values (?,?,?,?,?,?,?)");
					preparedStatement.setString(1, ((User) data).getUsername());
					preparedStatement.setString(2, ((User) data).getPassword());
					preparedStatement
							.setString(3, ((User) data).getFirstName());
					preparedStatement.setString(4, ((User) data).getLastName());
					preparedStatement.setString(5, ((User) data).getEmail());
					preparedStatement.setInt(6, ((User) data).getOrganization()
							.getId());
					preparedStatement.setString(7, ((User) data).getType());

				}

			} else if (data instanceof Sprint) {
				if (((Sprint) data).getScrum() != null) {
					preparedStatement = connection
							.prepareStatement("insert into sprint (scrumId, start, end) values (?,?,?)");
					preparedStatement.setInt(1, ((Sprint) data).getScrum()
							.getId());
					preparedStatement.setDate(2, ((Sprint) data).getStart());
					preparedStatement.setDate(3, ((Sprint) data).getEnd());

				} else {
					preparedStatement = connection
							.prepareStatement("insert into sprint (start,end) values (?,?)");
					preparedStatement.setDate(1, ((Sprint) data).getStart());
					preparedStatement.setDate(2, ((Sprint) data).getEnd());
				}
			}
			preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public AbstractModel getDataById(int id, int dataType) {
		AbstractModel data = null;
		try {
			switch (dataType) {
			case COMMENT:
				resultSet = statement
						.executeQuery("select * from comment where id = " + id);
				while (resultSet.next()) {
					String text = resultSet.getString("text");
					Date date = resultSet.getDate("date");
					int userId = resultSet.getInt("userId");
					User user = new User(userId,null,null,null,null,null,null,null,null);
					data = new Comment(id, user,date,text);
				}
				break;

			case USERSTORY:
				resultSet = statement
						.executeQuery("select * from userStory where id = "
								+ id);
				while (resultSet.next()) {
					String description = resultSet.getString("description");
					int sprintId = resultSet.getInt("sprintId");
					Sprint sprint = new Sprint(sprintId, null, null, null);
					data = new UserStory(id, sprint, description);
				}
				break;

			case SPRINT:
				resultSet = statement
						.executeQuery("select * from sprint where id = " + id);
				while (resultSet.next()) {
					Date start = resultSet.getDate("start");
					Date end = resultSet.getDate("end");
					int scrumId = resultSet.getInt("scrumId");
					Scrum scrum = new Scrum(scrumId, null, null);
					data = new Sprint(id, scrum, start, end);
				}

				break;

			case ORGANIZATION:
				resultSet = statement
						.executeQuery("select * from organization where id = "
								+ id);
				while (resultSet.next()) {
					String name = resultSet.getString("name");
					data = new Organization(id, name);

				}
				break;

			case PROJECT:
				resultSet = statement
						.executeQuery("select * from project where id = " + id);
				while (resultSet.next()) {
					String name = resultSet.getString("name");
					String description = resultSet.getString("description");
					byte[] specification = resultSet.getBytes("specification");
					String status = resultSet.getString("status");
					int owneId = resultSet.getInt("ownerId");
					User owner = new User(owneId, null, null, null, null, null,
							null, null, null);
					data = new Project(id, name, owner, description,
							specification, status);
				}
				break;

			case NOTIFICATION:
				resultSet = statement
						.executeQuery("select * from notification where id = "
								+ id);
				while (resultSet.next()) {
					String message = resultSet.getString("message");
					boolean seen = resultSet.getBoolean("seen");
					int senderId = resultSet.getInt("senderId");
					int receiverId = resultSet.getInt("receiverId");
					User sender = new User(senderId, null, null, null, null,
							null, null, null, null);
					User receiver = new User(receiverId, null, null, null,
							null, null, null, null, null);
					data = new Notification(id, sender, receiver, message, seen);
				}
				break;

			case ESTIMATION:
				resultSet = statement
						.executeQuery("select * from estimation where id = "
								+ id);
				while (resultSet.next()) {
					int time = resultSet.getInt("time");
					int priority = resultSet.getInt("priority");
					int taskId = resultSet.getInt("taskId");
					int userId = resultSet.getInt("userId");
					User user = new User(userId, null, null, null, null, null,
							null, null, null);
					Task task = new Task(taskId, null, null, null, null);
					data = new Estimation(id, user, task, time, priority);
				}
				break;

			case SCRUM:
				resultSet = statement
						.executeQuery("select * from scrum where id = " + id);
				while (resultSet.next()) {
					String name = resultSet.getString("name");
					int projectId = resultSet.getInt("projectId");
					Project project = new Project(projectId, null, null, null,
							null, null);
					data = new Scrum(id, name, project);
				}
				break;
			case TASK:
				resultSet = statement
						.executeQuery("select * from task where id = " + id);
				while (resultSet.next()) {
					String description = resultSet.getString("description");
					String status = resultSet.getString("status");
					int userId = resultSet.getInt("userId");
					User user = new User(userId, null, null, null, null, null,
							null, null, null);
					int userStoryId = resultSet.getInt("userstory");
					UserStory userStory = new UserStory(userStoryId, null, null);
					data = new Task(id, userStory, description, user, status);
				}
				break;
			case USER:
				resultSet = statement
						.executeQuery("select * from user where id = " + id);
				while (resultSet.next()) {
					String username = resultSet.getString("username");
					String password = resultSet.getString("password");
					String lastName = resultSet.getString("lastName");
					String firstName = resultSet.getString("firstName");
					String email = resultSet.getString("email");
					String type = resultSet.getString("type");
					int scrumId = resultSet.getInt("scrumId");
					Scrum scrum = null;
					if (scrumId != 0) {
						scrum = new Scrum(scrumId, null, null);
					}
					int organizationId = resultSet.getInt("organizationId");
					Organization organization = new Organization(
							organizationId, null);
					data = new User(id, username, password, firstName,
							lastName, email, organization, scrum, type);
				}
				break;

			default:
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
		return data;
	}

	public ArrayList<AbstractModel> getAll(int dataType) {
		ArrayList<AbstractModel> dataList = null;
		try {
			switch (dataType) {
			case COMMENT:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from comment");
					while (resultSet.next()) {
						String text = resultSet.getString("text");
						Date date = resultSet.getDate("date");
						int userId = resultSet.getInt("userId");
						int id = resultSet.getInt("id");
						User user = new User(userId,null,null,null,null,null,null,null,null);
						dataList.add(new Comment(id, user,date,text));
					}
				break;

			case USERSTORY:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from userStory");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String description = resultSet.getString("description");
					int sprintId = resultSet.getInt("sprintId");
					Sprint sprint = new Sprint(sprintId, null, null, null);
					UserStory newUserStory = new UserStory(id, sprint,
							description);
					dataList.add(newUserStory);
				}
				break;

			case ORGANIZATION:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement
						.executeQuery("select * from organization");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String name = resultSet.getString("name");
					Organization newOrganization = new Organization(id, name);
					dataList.add(newOrganization);
				}
				break;

			case PROJECT:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from project");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String name = resultSet.getString("name");
					String description = resultSet.getString("description");
					byte[] specification = resultSet.getBytes("specification");
					String status = resultSet.getString("status");
					int owneId = resultSet.getInt("ownerId");
					User owner = new User(owneId, null, null, null, null, null,
							null, null, null);
					Project newProject = new Project(id, name, owner,
							description, specification, status);
					dataList.add(newProject);
				}
				break;

			case NOTIFICATION:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement
						.executeQuery("select * from notification");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String message = resultSet.getString("message");
					boolean seen = resultSet.getBoolean("seen");
					int senderId = resultSet.getInt("senderId");
					int receiverId = resultSet.getInt("receiverId");
					User sender = new User(senderId, null, null, null, null,
							null, null, null, null);
					User receiver = new User(receiverId, null, null, null,
							null, null, null, null, null);

					Notification newNotification = new Notification(id, sender,
							receiver, message, seen);
					dataList.add(newNotification);
				}
				break;

			case ESTIMATION:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from estimation");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					int time = resultSet.getInt("time");
					int priority = resultSet.getInt("priority");
					int taskId = resultSet.getInt("taskId");
					int userId = resultSet.getInt("userId");
					User user = new User(userId, null, null, null, null, null,
							null, null, null);
					Task task = new Task(taskId, null, null, null, null);
					Estimation newEstimation = new Estimation(id, user, task,
							time, priority);
					dataList.add(newEstimation);
				}
				break;

			case SCRUM:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from scrum");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String name = resultSet.getString("name");
					int projectId = resultSet.getInt("projectId");
					Project project = new Project(projectId, null, null, null,
							null, null);
					Scrum newScrum = new Scrum(id, name, project);
					dataList.add(newScrum);
				}
				break;

			case TASK:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from task");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String description = resultSet.getString("description");
					String status = resultSet.getString("status");
					int userId = resultSet.getInt("userId");
					User user = new User(userId, null, null, null, null, null,
							null, null, null);
					int userStoryId = resultSet.getInt("userstory");
					UserStory userStory = new UserStory(userStoryId, null, null);
					Task newTask = new Task(id, userStory, description, user,
							status);
					dataList.add(newTask);
				}
				break;

			case USER:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from user");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					String username = resultSet.getString("username");
					String password = resultSet.getString("password");
					String lastName = resultSet.getString("lastName");
					String firstName = resultSet.getString("firstName");
					String email = resultSet.getString("email");
					String type = resultSet.getString("type");
					int organizationId = resultSet.getInt("organizationId");
					Organization organization = new Organization(
							organizationId, null);
					int scrumId = resultSet.getInt("scrumId");
					Scrum scrum = null;
					if (scrumId != 0) {
						scrum = new Scrum(scrumId, null, null);
					}
					User newUser = new User(id, username, password, firstName,
							lastName, email, organization, scrum, type);
					dataList.add(newUser);
				}
				break;

			case SPRINT:
				dataList = new ArrayList<AbstractModel>();
				resultSet = statement.executeQuery("select * from sprint");
				while (resultSet.next()) {
					int id = resultSet.getInt("id");
					Date start = resultSet.getDate("start");
					Date end = resultSet.getDate("end");
					int scrumId = resultSet.getInt("scrumId");
					Scrum scrum = new Scrum(scrumId, null, null);
					Sprint newSprint = new Sprint(id, scrum, start, end);
					dataList.add(newSprint);
				}
				break;

			default:
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return dataList;
	}

	public void updateData(AbstractModel data) {
		try {
			if (data instanceof Comment) {
				preparedStatement = connection
						.prepareStatement("update comment set userId=?, date=?, text=? where id=?");
				preparedStatement.setInt(1, ((Comment) data).getUser().getId());
				preparedStatement.setDate(2,((Comment) data).getDate());
				preparedStatement.setString(3, ((Comment) data).getText());
				preparedStatement.setInt(4, ((Comment) data).getId());

			} else if (data instanceof UserStory) {
				if (((UserStory) data).getSprint() != null) {
					preparedStatement = connection
							.prepareStatement("update userStory set description=?, sprintId=? where id=?");
					preparedStatement.setString(1,
							((UserStory) data).getDescription());
					preparedStatement.setInt(2, ((UserStory) data).getSprint()
							.getId());
					preparedStatement.setInt(3, ((UserStory) data).getId());
				} else {
					preparedStatement = connection
							.prepareStatement("update userStory set description=? where id=?");
					preparedStatement.setString(1,
							((UserStory) data).getDescription());
					preparedStatement.setInt(2, ((UserStory) data).getId());
				}
			}

			else if (data instanceof Project) {
				if (((Project) data).getOwner() != null) {
					preparedStatement = connection
							.prepareStatement("update project set name=?, ownerId=?, description=?, specification=?, status=? where id=?");
					preparedStatement.setString(1, ((Project) data).getName());
					preparedStatement.setInt(2, ((Project) data).getOwner()
							.getId());
					preparedStatement.setString(3,
							((Project) data).getDescription());
					preparedStatement.setBytes(4,
							((Project) data).getSpecification());
					preparedStatement
							.setString(5, ((Project) data).getStatus());
					preparedStatement.setInt(6, ((Project) data).getId());
				} else {
					preparedStatement = connection
							.prepareStatement("update project set name=?, description=?, specification=?, status=? where id=?");
					preparedStatement.setString(1, ((Project) data).getName());
					preparedStatement.setString(2,
							((Project) data).getDescription());
					preparedStatement.setBytes(3,
							((Project) data).getSpecification());
					preparedStatement
							.setString(4, ((Project) data).getStatus());
					preparedStatement.setInt(5, ((Project) data).getId());
				}
			} else if (data instanceof Organization) {
				preparedStatement = connection
						.prepareStatement("update organization set name=? where id=?");
				preparedStatement.setString(1, ((Organization) data).getName());
				preparedStatement.setInt(2, ((Organization) data).getId());
		

		} else if (data instanceof Sprint) {
			if (((Sprint) data).getScrum() != null) {
                preparedStatement = connection
                                .prepareStatement("update sprint set scrumId=?, start=?, end=? where id=?");
                preparedStatement.setInt(1, ((Sprint) data).getScrum().getId());
                preparedStatement.setDate(2, ((Sprint) data).getStart());
                preparedStatement.setDate(3, ((Sprint) data).getEnd());
                preparedStatement.setInt(4, ((Sprint) data).getId());
                preparedStatement.executeUpdate();
        } else {
                preparedStatement = connection
                                .prepareStatement("update sprint set start=?, end=? where id=?");
                preparedStatement.setDate(1, ((Sprint) data).getStart());
                preparedStatement.setDate(2, ((Sprint) data).getEnd());
                preparedStatement.setInt(3, ((Sprint) data).getId());
        }

		}
			
			else if (data instanceof Notification) {
				preparedStatement = connection
						.prepareStatement("update notification set senderId=?, receiverId=?, message=?, seen=? where id=?");
				preparedStatement.setInt(1, ((Notification) data).getSender()
						.getId());
				preparedStatement.setInt(2, ((Notification) data).getReceiver()
						.getId());
				preparedStatement.setString(3,
						((Notification) data).getMessage());
				preparedStatement
						.setBoolean(4, ((Notification) data).getSeen());
				preparedStatement.setInt(5, ((Notification) data).getId());

			} else if (data instanceof Estimation) {
				preparedStatement = connection
						.prepareStatement("update estimation set userId=?, taskId=?, time=?, priority=? where id=?");
				preparedStatement.setInt(1, ((Estimation) data).getUser()
						.getId());
				preparedStatement.setInt(2, ((Estimation) data).getTask()
						.getId());
				preparedStatement.setInt(3, ((Estimation) data).getTime());
				preparedStatement.setInt(4, ((Estimation) data).getPriority());
				preparedStatement.setInt(5, ((Estimation) data).getId());

			} else if (data instanceof Scrum) {
				if (((Scrum) data).getProject() != null) {
					preparedStatement = connection
							.prepareStatement("update scrum set name=?, projectId=? where id=?");
					preparedStatement.setString(1, ((Scrum) data).getName());
					preparedStatement.setInt(2, ((Scrum) data).getProject()
							.getId());
					preparedStatement.setInt(3, ((Scrum) data).getId());
				} else {
					preparedStatement = connection
							.prepareStatement("update scrum set name=? where id=?");
					preparedStatement.setString(1, ((Scrum) data).getName());
					preparedStatement.setInt(2, ((Scrum) data).getId());
				}

			} else if (data instanceof Task) {
				if (((Task) data).getUser() != null) {
					preparedStatement = connection
							.prepareStatement("update task set userstory=?, description=?, userId=?, status=? where id=?");
					preparedStatement.setInt(1, ((Task) data).getUserStory()
							.getId());
					preparedStatement.setString(2,
							((Task) data).getDescription());
					preparedStatement
							.setInt(3, ((Task) data).getUser().getId());
					preparedStatement.setString(4, ((Task) data).getStatus());
					preparedStatement.setInt(5, ((Task) data).getId());
				} else {
					preparedStatement = connection
							.prepareStatement("update task set userstory=?, description=?, status=? where id=?");
					preparedStatement.setInt(1, ((Task) data).getUserStory()
							.getId());
					preparedStatement.setString(2,
							((Task) data).getDescription());
					preparedStatement.setString(3, ((Task) data).getStatus());
					preparedStatement.setInt(4, ((Task) data).getId());
				}
			}

			else if (data instanceof User) {
				if (((User) data).getScrum() != null) {
					preparedStatement = connection
							.prepareStatement("update user set username=?, password=?, firstName=?, lastName=?, email=?, scrumId=?, organizationId=?, type=? where id=?");
					preparedStatement.setString(1, ((User) data).getUsername());
					preparedStatement.setString(2, ((User) data).getPassword());
					preparedStatement
							.setString(3, ((User) data).getFirstName());
					preparedStatement.setString(4, ((User) data).getLastName());
					preparedStatement.setString(5, ((User) data).getEmail());
					preparedStatement.setInt(6, ((User) data).getScrum()
							.getId());
					preparedStatement.setInt(7, ((User) data).getOrganization()
							.getId());
					preparedStatement.setString(8, ((User) data).getType());
					preparedStatement.setInt(9, ((User) data).getId());
				} else {
					preparedStatement = connection
							.prepareStatement("update user set username=?, password=?, firstName=?, lastName=?, email=?, organizationId=?, type=? where id=?");
					preparedStatement.setString(1, ((User) data).getUsername());
					preparedStatement.setString(2, ((User) data).getPassword());
					preparedStatement
							.setString(3, ((User) data).getFirstName());
					preparedStatement.setString(4, ((User) data).getLastName());
					preparedStatement.setString(5, ((User) data).getEmail());
					preparedStatement.setInt(6, ((User) data).getOrganization()
							.getId());
					preparedStatement.setString(7, ((User) data).getType());
					preparedStatement.setInt(8, ((User) data).getId());
				}
			}
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteData(AbstractModel data) {
		try {
			if (data instanceof Comment) {
				if (getDataById(((Comment) data).getId(), COMMENT) != null) {
					preparedStatement = connection
							.prepareStatement("delete from comment where id = ?");
					preparedStatement.setInt(1, ((Comment) data).getId());
				}
			} else if (data instanceof UserStory) {
				if (getDataById(((UserStory) data).getId(), USERSTORY) != null) {
					preparedStatement = connection
							.prepareStatement("delete from userStory where id = ?");
					preparedStatement.setInt(1, ((UserStory) data).getId());
				}
			} else if (data instanceof Project) {
				if (getDataById(((Project) data).getId(), PROJECT) != null) {
					preparedStatement = connection
							.prepareStatement("delete from project where id = ?");
					preparedStatement.setInt(1, ((Project) data).getId());
				}

			} else if (data instanceof Organization) {
				if (getDataById(((Organization) data).getId(), ORGANIZATION) != null) {
					preparedStatement = connection
							.prepareStatement("delete from organization where id = ?");
					preparedStatement.setInt(1, ((Organization) data).getId());
				}
			} else if (data instanceof Notification) {
				if (getDataById(((Notification) data).getId(), NOTIFICATION) != null) {
					preparedStatement = connection
							.prepareStatement("delete from notification where id = ?");
					preparedStatement.setInt(1, ((Notification) data).getId());

				}
			} else if (data instanceof Estimation) {
				if (getDataById(((Estimation) data).getId(), ESTIMATION) != null) {
					preparedStatement = connection
							.prepareStatement("delete from estimation where id = ?");
					preparedStatement.setInt(1, ((Estimation) data).getId());
				}
			} else if (data instanceof Scrum) {
				if (getDataById(((Scrum) data).getId(), SCRUM) != null) {
					preparedStatement = connection
							.prepareStatement("delete from scrum where id = ?");
					preparedStatement.setInt(1, ((Scrum) data).getId());
				}
			} else if (data instanceof Task) {
				if (getDataById(((Task) data).getId(), TASK) != null) {
					preparedStatement = connection
							.prepareStatement("delete from task where id = ?");
					preparedStatement.setInt(1, ((Task) data).getId());
				}
			} else if (data instanceof User) {
				if (getDataById(((User) data).getId(), USER) != null) {
					preparedStatement = connection
							.prepareStatement("delete from user where id = ?");
					preparedStatement.setInt(1, ((User) data).getId());
				}
			
		} else if (data instanceof Sprint) {
			if (getDataById(((Sprint) data).getId(), SPRINT) != null) {
				preparedStatement = connection
						.prepareStatement("delete from sprint where id = ?");
				preparedStatement.setInt(1, ((Sprint) data).getId());
			}
		}
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}

	}

	public List<UserStory> getUserStoryBySprint(int sprintId) {
		ArrayList<UserStory> dataList = new ArrayList<UserStory>();
		try {
			resultSet = statement
					.executeQuery("select * from userStory where sprintId="
							+ sprintId);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String description = resultSet.getString("description");
				Sprint sprint = new Sprint(sprintId, null, null, null);
				UserStory newUserStory = new UserStory(id, sprint, description);
				dataList.add(newUserStory);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}

	public List<Task> getTaskByUserStory(int userStoryId) {
		ArrayList<Task> dataList = new ArrayList<Task>();
		try {
			resultSet = statement
					.executeQuery("select * from task where userstory="
							+ userStoryId);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String description = resultSet.getString("description");
				String status = resultSet.getString("status");
				int userId = resultSet.getInt("userId");
				User user = new User(userId, null, null, null, null, null,
						null, null, null);
				UserStory userStory = new UserStory(userStoryId, null, null);
				Task newTask = new Task(id, userStory, description, user,
						status);
				dataList.add(newTask);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}

	public User login(String text, String password) {
		User user = null;
		try {
			if (!text.contains("@"))
				resultSet = statement
						.executeQuery("select * from user where username='"
								+ text + "' and password='" + password + "'");
			else
				resultSet = statement
						.executeQuery("select * from user where email='" + text
								+ "' and password='" + password + "'");
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String username = resultSet.getString("username");
				String lastName = resultSet.getString("lastName");
				String firstName = resultSet.getString("firstName");
				String email = resultSet.getString("email");
				String type = resultSet.getString("type");
				int scrumId = resultSet.getInt("scrumId");
				Scrum scrum = null;
				if (scrumId != 0) {
					scrum = new Scrum(scrumId, null, null);
				}
				int organizationId = resultSet.getInt("organizationId");
				Organization organization = new Organization(organizationId,
						null);
				user = new User(id, username, password, firstName, lastName,
						email, organization, scrum, type);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return user;
	}

	public List<Sprint> getSprintByScrum(int scrumId) {
		ArrayList<Sprint> dataList = new ArrayList<Sprint>();
		try {
			resultSet = statement
					.executeQuery("select * from sprint where scrumId="
							+ scrumId);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				Date start = resultSet.getDate("start");
				Date end = resultSet.getDate("end");
				Scrum scrum = new Scrum(scrumId, null, null);
				Sprint newSprint = new Sprint(id, scrum, start, end);
				dataList.add(newSprint);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}

	public List<User> getUserByScrum(int scrumId) {
		ArrayList<User> dataList = new ArrayList<User>();
		try {
			resultSet = statement
					.executeQuery("select * from user where scrumId=" + scrumId);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String username = resultSet.getString("username");
				String password = resultSet.getString("password");
				String lastName = resultSet.getString("lastName");
				String firstName = resultSet.getString("firstName");
				String email = resultSet.getString("email");
				String type = resultSet.getString("type");
				int organizationId = resultSet.getInt("organizationId");
				Organization organization = new Organization(organizationId,
						null);
				Scrum scrum = new Scrum(scrumId, null, null);
				User newUser = new User(id, username, password, firstName,
						lastName, email, organization, scrum, type);
				dataList.add(newUser);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}
	
	public List<Comment> getCommentByScrum(int scrumId) {
		ArrayList<Comment> dataList = new ArrayList<Comment>();
		try {
			resultSet = statement.executeQuery("select c.id,c.userId,c.date,c.text from comment c, user u where c.userId=u.id and u.scrumId="+scrumId);
		while (resultSet.next()) {
			String text = resultSet.getString("text");
			Date date = resultSet.getDate("date");
			int userId = resultSet.getInt("userId");
			int id = resultSet.getInt("id");
			User user = new User(userId,null,null,null,null,null,null,null,null);
			dataList.add(new Comment(id, user,date,text));
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}

}
